%by amr

In this Section, we will discuss how to enforce the constraints that were described previously on the relational model
generated from the transition from UML/CM diagrams into relational models.

\subsubsection{Primary key constraint}

The intuition behind enforcing enforcing the primary key constraint is that we do not allow duplicate entries in a column or a set
of columns.
suppose a table contains two
columns \texttt{C1:C2}. then we use the following function to add some values to \texttt{C3}
\[
\texttt{C3 < =SUMPRODUCT((C1=RC1)*(C2=RC2))}
\]
This causes \texttt{RiC3} to contain the number of tuples from
\texttt{C1:C2} which are equal to {RiC1:RiC2} and are located at the
same level or above it. This number is 1 if and only if the row contains
the first occurrence of this tuple.
\[
\texttt{C4:C5 << =IF(RC3=1,RC[-3],"")}
\]
Now the first occurrences of tuples are copied into {C4:C5},
the other are replaced by null rows.
Therefore for every new entry to the table, this operation has to be done to check if the newly added entry violates
the primary key constraint or not.

\subsubsection{Foriegn key constraint}

Implementing a constraint to check for foreign key as we will have to do a semijoin over two tables in order to make
sure that the foreign key corresponds to a primary key in the targeted table. and then using the \texttt{IF/3} function,
we can check if that key exists in the target table or not. However, the problem
appears when an entry is deleted from the foreign key table, then the system
should issue a warning to warn the user of the inconsistency that might happen
after the deletion.

\subsubsection{Cardinality constraint}

The cardinality constraint can be checked using the function \texttt{SUMPRODUCT/1} such that for the needed check over
the columns, we can get the number of the similar entries over a set of columns. Therefore if the cardinality check is not
satisfied, then the system can issue a warning to the user that it is not satisfied.

\subsubsection{Null data}

In order to deal with the null cell, for any non optional column, we can use the function \texttt{IF/3} that we can use to check the
condition if the cells in the intended column are equal \texttt{NULL}, if so, then the system should throw an error.
\subsubsection{Depending Columns}
The expression of the form:
\[
Table_i[col_{i'_1},\ldots,col_{i'_m}] \mbox{~with~}
Table_i.[col_{i_1},\ldots,col_{i_n}]
\mbox{~is~} 
\]
\[
Table_j[col_{j'_1},\ldots,col_{j'_m}] \mbox{~with~}
Table_j.[col_{j_1},\ldots,col_{j_n}]
\]
can be handled using the $\texttt{VLOOKUP(LookUpValue, TableArray, IndexCol,
RangeLookUp)}$ in order to get in the specified columns ($Table_i[col_{i'_1},\ldots,col_{i'_m}]$)
the coreesponding values from the source table
$Table_j[col_{j'_1},\ldots,col_{j'_m}]$ by looking up the columns
$Table_i.[col_{i_1},\ldots,col_{i_n}]$ in the source table. This function only
accepts the search of single column but the multiple columns case can be
simulated by using a hidden column which is the concatenation of the columns 
that are needed to be looked up.

\subsection{Google scripts}

Another way which is more convenient for checking the constraints and depending
columns is using the scripting language provided by Google in Google
spreadsheets.

The scripting language is javascript. With some special API which allows
accessing the cells in the spreadsheets, performing operations to read and write
information to the cells in the spreadsheet. Moreover, we can program warning
boxes and notifications. These scripts are triggered by the events that
occurs during the interaction with the spreadsheet.

For example, to check the primary key constraint, a small loop will go over the
corresponding column and check that all the values are different.

In the following figure which is taken from \cite{googlesp}, we can see the
warning sign that appears for the user.
 
\includegraphics[scale=0.3]{google.jpg}
%\subsubsection{Subset Constraint}


